import pandas as pd

pd.set_option('display.unicode.east_asian.width', True)
pd.set_option('display.unicode.ambiguous_as_wide', True)

pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 100)
pd.set_option('display.width', 1000)

excel = pd.read_excel('resources/快递发货单.xlsx', usecols=['客户名称', '快递地址'])
print(excel)
print('---------------------------')
print(excel['快递地址'])  # Series对象

"""
一列数据变成多列数据
"""
# expand=True表示结果转成DataFrame对象
new_excel = excel['快递地址'].str.split(' ', expand=True)
print(new_excel)
excel['省'] = new_excel[0]
excel['市'] = new_excel[1]
excel['区'] = new_excel[2]
excel['具体位置'] = new_excel[3]
print(excel)

data = {
    'a': [1, 2, 3, 4, 5],
    'b': [(1, 2), (2, 3), (3, 4), (5, 6), (7, 8)]
}
df = pd.DataFrame(data=data)
print(df)
df[['b1', 'b2']] = df['b'].apply(pd.Series)
print(df)

# join()与apply()
df = df.join(df['b'].apply(pd.Series))
print(df)

"""
行列转换 stack()、unstack()、pivot(index=None, columns=None, values=None)
"""
excel = pd.read_excel('resources/grade.xlsx')
print(excel)

excel = excel.set_index(['班级', '序号'])
print(excel)

excel1 = excel.stack()  # 将 列 变成 行
print(excel1)

excel = pd.read_excel('resources/grade2.xls', sheet_name='英语2')
print(excel)

excel = excel.set_index(['班级', '序号', 'Unnamed: 2'])
print(excel)
print(excel.unstack())  # 将 行 变成 列

# pivot(index=None, columns=None, values=None)
# index设置行索引，columns设置列索引，values设置值
excel = pd.read_excel('resources/grade3.xlsx')
print(excel)

print(excel.pivot(index='序号', columns='班级', values='得分'))

"""
DataFrame --> 字典、列表、元组、HTML
"""
# DataFrame --> 字典
excel = pd.read_excel('resources/电脑配件销售记录.xlsx')
print(excel)

excel1 = excel.groupby('产品名称')['数量'].sum()  # 根据产品名称分组，统计数量
print(excel1.to_dict())  # 转成字典

# DataFrame --> 列表
excel2 = excel['产品名称']
print(excel2)  # Series
print(excel2.values)  # array

print(excel2.tolist())  # list

# DataFrame --> 元组
excel = pd.read_excel('resources/电脑配件销售记录.xlsx')
print(excel)
excel3 = excel[['产品名称', '销售员', '数量']]
print(excel3)

print(excel3.values)  # array
t = [tuple(x) for x in excel3.values]
print(t)

# DataFrame --> HTML
excel = pd.read_excel('resources/电脑配件销售记录.xlsx')
print(excel)

excel.to_html('resources/电脑配件销售记录.html', header=True, index=False)
